const Router = require('koa-router');
const router = new Router();
const tool = require('../tools/tool')
const {db, QueryTypes} = require("../models/db");
const pageParams = require("../tools/cols");
const _ = require("lodash");
const tools = require("../tools/tool");
// 删除用户
router.post('/list/cmx', async ctx => {
    try {
        let body = ctx.request.body
        let user = ctx.user
        let search = body.search;
        let isExport = body.isExport;
        let searchTxt = search.searchTxt ? search.searchTxt : '';
        let tenantId = user.tenantId;
        let beginDate = search.beginDate ? search.beginDate : tool.getDay('', -15)
        let endDate = search.endDate ? search.endDate : tool.getDay()
        let buId = search.buId ? search.buId : 0;
        let salesId = search.salesId ? search.salesId : 0;
        let sql = `exec report_cus_bills_detail_all @tenantId=${tenantId},@beginDate='${beginDate}',@endDate='${endDate}',@buId=${buId},@salesId=${salesId}`;
        let list = await db.query(sql, {type: QueryTypes.SELECT})
        let retGp = tool.xe.groupBy(list, 'buId')
        let results = []
        for(let item in retGp) {
            let hasqc = false
            for (let i = 0; i < retGp[item].length; i++) {
                if(retGp[item][i].billType === '期初') {
                    hasqc = true
                    break
                }
            }
            if(retGp[item].length === 1 && hasqc && tool.xe.toNumber(retGp[item][0].ye) == 0) {
                continue
            }
            if(!hasqc) {
                results.push({
                    buId: retGp[item][0].buId,
                    buName: retGp[item][0].buName,
                    billType: '期初',
                    ye: ''
                })
            }
            retGp[item].map(row => {
                results.push(row)
            })
        }
        results.map(row => {
            if(tool.xe.toNumber(row.amount) == 0) {
                row.amount = ''
            }
            if(tool.xe.toNumber(row.hxAmount) == 0) {
                row.hxAmount = ''
            }
            if(tool.xe.toNumber(row.ysAdd) == 0) {
                row.ysAdd = ''
            }
            if(tool.xe.toNumber(row.ye) == 0) {
                row.ye = ''
            }
            row.ysAdd = tool.xe.toFixed(row.ye, 2)
            if(row.billType !=='期初') {
                row.ye = ''
                row.amount = tool.xe.toFixed(row.amount,2)
            }
            else {
                row.ysAdd = ''
            }
        })
        let lastRet = []
        for (let i = 0; i < results.length; i++) {
            lastRet.push(results[i])
            let row = {}
            row.buId = results[i].buId
            row.billType = '小计'
            row.hxAmount = ''
            row.buName = ''
            row.ye = ''
            // 要每个商户的最后一行加一行小计
            if(i+1 < results.length) {
                // 如果下一行是另一个商户了，加一行小计
                if(results[i+1].buId !== results[i].buId) {
                    lastRet.push(row)
                }
            }
            else {
                lastRet.push(row)
            }
        }
        let sub = 0
        for (let i = 0; i < lastRet.length - 1; i++) {
            if(lastRet[i+1].billType !== '期初' && lastRet[i+1].billType !== '小计') {
                let am = tool.xe.add(lastRet[i].ye, lastRet[i+1].ysAdd)
                lastRet[i+1].ye = am == 0 ?'': tool.xe.toFixed(am, 2)
                lastRet[i+1].buName = ''
            }
            if(lastRet[i].billType === '小计') {
                lastRet[i].ye = lastRet[i-1].ye==0?'':tool.xe.toFixed(lastRet[i-1].ye, 2)
            }
            if(lastRet[i].billType === '期初') {
                lastRet[i].ye = lastRet[i].ye == 0 ? '' : tool.xe.toFixed(lastRet[i].ye, 2)
                sub = 0
            }
            if(tool.xe.toNumber(lastRet[i].hxAmount) !== 0) {
                lastRet[i].hxAmount = tool.xe.toFixed(lastRet[i].hxAmount, 2)
            }
            sub = tool.xe.add(sub, lastRet[i].ysAdd)
            if(lastRet[i].billType === '小计') {
                lastRet[i].ysAdd = sub == 0 ?'':tool.xe.toFixed(sub, 2)
            }
        }
        if (isExport) {
            ctx.request.body.whichPage = '对账明细'
            let params = pageParams(ctx, tools)
            return await tool.exportOut(params, list, ctx)
        }
        else {
            return ctx.suc('', lastRet);
        }
    } catch (e) {
        console.log('list', e)
        return ctx.err('无返回数据')
    }
})
// 页面表单配置
router.post('/list/getParams', async ctx => {
    let page = ctx.request.body.page;
    let params = await db.models['formCust'].findOne({
        where: {
            page: page
        }
    })
    let fieldsArr = [
        {
            field: 'id',
            title: 'ID',
            isKey: true,
            visible: false,
            add: false,
            edit: false
        },
        {
            field: 'seq',
            type: 'seq',
            title: '#',
            width: 60,
            align: 'center',
            visible: true,
            fixed: 'left'
        },
        {
            field: 'checked',
            type: 'checkbox',
            title: '',
            width: 40,
            fixed: 'left',
            visible: true,
            align: 'center'
        },
    ]
    // let ctimeField = "{field: 'ctime', title: '创建时间',excelOut:true,sortable:true, width: 105 }";
    // let utimeField = "{field: 'utime', title: '更新时间',excelOut:true,sortable:true,  width: 105 }";
    // let dtimeField = "{field: 'dtime', title: '删除时间',excelOut:true,sortable:true,   width: 105 }";
    // let fileName = params.name; // 导出的文件名称
    let cols = JSON.parse(params.fields) // 查询。编辑的字段
    cols.map(item => {
        fieldsArr.push(item)
    })
    let toolbarstr = params.toolbar; // toolbar
    // let contextmenustr = params.contextmenu; // contextmenu
    let btns = [
        // {
        //     code: 'refresh',
        //     name: '刷新',
        //     prefixIcon: 'fa fa-refresh',
        //     title: '重置所有条件并刷新当前页',
        //     disabled: false
        // },
        {
            code: 'search',
            name: '查询',
            prefixIcon: 'fa fa-search',
            title: '高级搜索',
            disabled: false
        },
        {
            code: 'auth',
            name: '权限',
            prefixIcon: 'fa fa-user-o',
            title: '编辑权限',
            disabled: false
        },
        {
            code: 'setPwd',
            name: '密码',
            prefixIcon: 'fa fa-user-o',
            title: '设置密码',
            disabled: false
        },
        {
            code: 'add',
            name: '新增',
            prefixIcon: 'fa fa-plus',
            title: '新增 1 条数据',
            disabled: false
        },
        {
            code: 'edit',
            name: '编辑',
            prefixIcon: 'fa fa-edit',
            title: '编辑 1 条数据',
            disabled: false
        },
        {
            code: 'accounted',
            name: '记账',
            prefixIcon: 'fa fa-rmb',
            title: '记账',
            disabled: false
        },
        {
            code: 'del',
            name: '删除',
            prefixIcon: 'fa fa-trash',
            title: '删除选中条目',
            disabled: false
        },
        {
            code: 'copy',
            name: '复制',
            prefixIcon: 'fa fa-copy',
            title: '复制单元格内容',
            disabled: false
        },
        {
            code: 'import',
            name: '导入',
            prefixIcon: 'fa fa-upload',
            title: '导入数据',
            disabled: false
        },
        {
            code: 'export',
            name: '导出',
            prefixIcon: 'fa fa-download',
            title: '导出数据',
            disabled: false
        },
        {
            code: 'print',
            name: '打印',
            prefixIcon: 'fa fa-print',
            title: '打印页面内容',
            disabled: false
        },
        {
            code: 'columns',
            name: '选列',
            prefixIcon: 'fa fa-columns',
            title: '选择要显示的列',
            disabled: false
        },
        {
            code: 'close',
            name: '关闭',
            prefixIcon: 'fa fa-close',
            title: '关闭',
            disabled: false
        },
        {
            code: 'lock',
            name: '锁屏',
            prefixIcon: 'fa fa-lock',
            title: '离开并锁定屏幕',
            disabled: false
        },
    ]
    let btnList = []
    for (let i = 0; i < btns.length; i++) {
        let btn = btns[i];
        if (toolbarstr.indexOf(btn.code) !== -1) {
            btnList.push(btn)
        }
    }
    let contextmenu = []
    for (let i = 0; i < fieldsArr.length; i++) {
        let it = fieldsArr[i]
        if (it.field === 'beginDate') {
            it.default = tool.getDay('', -15)
        }
        if (it.field === 'endDate') {
            it.default = tool.getDay()
        }
    }
    let ret = {
        btnList,
        contextmenu,
        columns: fieldsArr,
        params: params
    }
    return ctx.suc('', ret)
})
// 销售明细
router.post('/list/s-detail', async ctx => {
    try {
        let user = ctx.user;
        let body = ctx.request.body;
        let sort = body.sort;
        let isExport = body.excel;
        let tenantId = user.tenantId;
        let search = body.formData;
        let searchTxt = search.searchTxt ? search.searchTxt : '';
        let beginDate = search.beginDate ? search.beginDate : '';
        let endDate = search.endDate ? search.endDate : '';
        let transType = search.transType ? search.transType : '';
        let buId = search.buId ? search.buId : '';
        let invId = search.invId ? search.invId : '';
        let salesId = search.salesId ? search.salesId : '';
        let contactCatId = search.contactCatId ? search.contactCatId : '';
        let deliverId = search.deliverId ? search.deliverId : '';
        let skBeg = search.skBeg ? search.skBeg : '';
        let skEnd = search.skEnd ? search.skEnd : '';
        let remark = search.remark ? search.remark : '';
        let billNo = search.billNo ? search.billNo : '';
        let ctime = tool.getDay();
        let params = pageParams(ctx, tools)
        if (!beginDate) {
            beginDate = tool.getDay('', -15);
        }
        if (!endDate) {
            endDate = ctime;
        }
        let mainSql = `select a.id,b.id detailId,a.billDate,a.billNo,a.transTypeName,a.buId,co.name buName,co.categoryName contactCat,b.invId,a.salesId,ad.name salesName,a.deliverIds,a.deliverNames,
    b.spec,b.unit,b.qty,-b.qtyReal qtyReal,b.price,b.remark,b.send,b.amount,
case when a.transType=150601 then b.amount when a.transType=150602 then -b.amount else case when b.trans='换入' then -b.amount else b.amount end end subAmount,b.assQty,
        (b.calNum*b.qtyReal) retailQty,gs.unit1,gs.unit2,gs.name invName,gs.categoryName,
case when a.accAmount<>0 then concat(a.billDate,' '   ,c.skDate) else c.skDate  end skDate ,g.barCode1 
    from invoice a  inner JOIN invoice_info b on a.id=b.iid  and a.tenantId=${tenantId} and a.billType='SALE' and a.billState='accounted' inner join goods g on a.tenantId=g.tenantId and b.invId=g.id  `;
        if (buId) {
            mainSql = mainSql + ` and a.buId=${buId} `;
        }
        if (salesId) {
            mainSql = mainSql + ` and a.salesId=${salesId} `;
        }
        if (invId) {
            mainSql = mainSql + ` and b.invId=${invId} `;
        }
        if(billNo) {
            mainSql = mainSql + ` and a.billNo like '%${billNo}%' `
        }
        if(remark) {
            mainSql = mainSql + ` and a.subRemark like '%${remark}%' `
        }
        if(contactCatId) {
            let gids = await tool.getChildIds(contactCatId, 'contact_cat')
            if(gids.length > 0) {
                mainSql = mainSql + ` and a.buId in (select id from contact where categoryId in (${gids.join(',')}))  `
            }
        }
        let goodsCatId = search.goodsCatId ? search.goodsCatId : 0;
        if(goodsCatId) {
            let gids = await tool.getChildIds(goodsCatId, 'goods_cat')
            if(gids.length > 0) {
                mainSql = mainSql + ` and b.invId in (select id from goods where categoryId in (${gids.join(',')}))  `
            }
        }
        if (transType) {
            mainSql = mainSql + ` and a.transType=${transType} `;
        }
        if (deliverId) {
            let deliver = await db.models['admin'].findOne({
                where: {
                    id: deliverId
                },
                attributes: ['name']
            })
            mainSql = mainSql + " and a.deliverNames like '%" + deliver.name + "%' ";
        }
        if (tool.xe.trim(searchTxt)) {
            mainSql = mainSql + ` and a.postData like '%${tool.xe.trim(searchTxt)}%' `;
        }
        if (beginDate) {
            mainSql = mainSql + " and a.billDate >= '" + beginDate + "' ";
        }
        if (endDate) {
            mainSql = mainSql + " and a.billDate <= '" + endDate + "' ";
        }
        mainSql = mainSql + ` left join (select a.id,a.unit1,a.unit2,a.name,b.name categoryName from goods a left join goods_cat b on a.categoryId=b.id )  gs on b.invId=gs.id 
        left join (select * from v_receipt_skDate where tenantId=${tenantId}) c on a.id=c.billId
         left join admin ad on a.salesId=ad.id  left join (select a.id,a.name,b.name categoryName from contact a left join contact_cat b on a.categoryId=b.id) co on a.buId=co.id   `;

        if(sort && typeof sort === 'object' && sort.length > 0) {
            let ors = []
            sort.map(row => {
                if(row.field === 'durationStr') {
                    ors.push(` duration ${row.order} `)
                }
                else {
                    ors.push(` ${row.field} ${row.order} `)
                }
            })
            mainSql = mainSql + `  order by ${ors.join(',') } `
        }
        else {
            mainSql = mainSql + ' order by a.billDate asc,id asc '
        }

        if (isExport) {
            let ret = await db.query(mainSql, {type: QueryTypes.SELECT, raw: true})
            ret.map(row => {
                row.qty = tool.xe.toNumber(row.qty)
                row.qtyReal = tool.xe.toNumber(row.qtyReal)
                row.subAmount = tool.xe.toNumber(row.subAmount)
                row.amount = tool.xe.toNumber(row.amount)
                row.price = tool.xe.toNumber(row.price)
            })
            return await tools.exportOut(params, ret, ctx)
        }
        let list = await db.query(mainSql, {type: QueryTypes.SELECT, raw: false})
        list.map(row => {
            row.checked = false
        })
        return ctx.suc('', {
            list: list
        });
    }
    catch (e) {
        console.log('销售明细', e)
        return ctx.err('查询失败')
    }
})



module.exports = router;
